/**
 * 
 */
package cn.edu.nju.ws.sview.wyge.relations.adataset.bookmark;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import cn.edu.nju.ws.sview.cache.UserNameIDCache;
import cn.edu.nju.ws.sview.database.DBConnectionFactory;
import cn.edu.nju.ws.sview.views.User;
import cn.edu.nju.ws.sview.wyge.relations.datasets.DatasetItem;

/**
 * @author Weiyi Ge (geweiyi@gmail.com) 2012-7-23
 * 
 */
public class DatasetBookmark {
	public static String getBookmarkNameById(int bmId){
		String bmName = null;
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement pstmt = conn.prepareStatement("select name from ds_bookmark where id=?");
			pstmt.setInt(1, bmId);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				bmName = rs.getString(1);
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return bmName;
	}
	public static ArrayList<Integer> getBookmarkIdsByUserId(int uid){
		ArrayList<Integer> bmIdList = new ArrayList<Integer>();
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement pstmt = conn.prepareStatement("select id from ds_bookmark where user_id=? order by id");
			pstmt.setInt(1, uid);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				bmIdList.add(rs.getInt(1));
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return bmIdList;
	}
	/**
	 * 判断用户是否在当前ds当前lang下做过bookmark
	 * @param datasetId
	 * @param ds_type
	 * @param uid
	 * @return
	 */
	public static boolean hasBookmarkedOnItemLanguage(int uid,int datasetId, String ds_type, String lang){
		boolean bookmarked = false;
		try {
			Connection conn = DBConnectionFactory.getConnection();
			PreparedStatement pstmt = conn.prepareStatement("select id from ds_bookmark where user_id=? and lang=? and id in (select ds_bm_id from ds_bookmark_dataset where ds_id=? and ds_type=?)");
			pstmt.setInt(1, uid);
			pstmt.setString(2, lang);
			pstmt.setInt(3, datasetId);
			pstmt.setString(4, ds_type);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				bookmarked = true;
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return bookmarked;
	}
	public static ArrayList<DatasetItem> getBookmarkItems(String userName, String lang, int start, int length) {
		int userId = User.getUserIdByName(userName);
		return DatasetBookmark.getBookmarkItems(userId,lang,start,length);
	}
	public static ArrayList<DatasetItem> getBookmarkItems(int userId, String lang, int start, int length) {
		ArrayList<DatasetItem> dsList = new ArrayList<DatasetItem>();
		PreparedStatement bmidps;
		try {
			Connection con = DBConnectionFactory.getConnection();
			StringBuffer sql = new StringBuffer("select id,name from ds_bookmark where user_id=? and lang=? order by id ");
			if(length>=0){
				sql.append(String.format(" limit %d,%d", start, length));
			}
			bmidps = con.prepareStatement(sql.toString());//("select id,name from ds_bookmark where user_id=? and lang=? limit ?,?");
			bmidps.setInt(1, userId);
			bmidps.setString(2, lang);
//			bmidps.setInt(3, start);
//			bmidps.setInt(4, length);
			ResultSet rs = bmidps.executeQuery();
			DatasetItem ds = null;
			while(rs.next()){
				ds = DatasetBookmark.getDatasetItemOfBookmark(rs.getInt(1));//get dataset
//				System.out.println("name:"+rs.getString(2));
				ds.setShowName(rs.getString(2));//.setName(rs.getString(2));//set bookmarkname
				if(ds != null){
					dsList.add(ds);
				}
			}
			rs.close();
			bmidps.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return dsList;
	}
	public static DatasetItem getDatasetItemOfBookmark(String bookmarkName, String userName){
		int bookmarkId = DatasetBookmark.getBookmarkID(bookmarkName, userName);
		return DatasetBookmark.getDatasetItemOfBookmark(bookmarkId);
	}
	public static DatasetItem getDatasetItemOfBookmark(int bookmarkId) {
		DatasetItem ds = null;
		PreparedStatement pstmt;
		try {
			Connection con = DBConnectionFactory.getConnection();
			pstmt = con.prepareStatement("select ds_id, ds_type from ds_bookmark_dataset where ds_bm_id=?");
			PreparedStatement getUri = con.prepareStatement("select uri from uri where id=?");
//			PreparedStatement getDsName = con.prepareStatement("select name from custom_dataset where id=?");
			pstmt.setInt(1, bookmarkId);
			ResultSet rs = pstmt.executeQuery();
			ResultSet dnrs = null;
			if(rs.next()){
				int ds_id = rs.getInt(1);
				String ds_type = rs.getString(2);
				if(ds_type.contentEquals("uri")){
					getUri.setInt(1, ds_id);
					dnrs = getUri.executeQuery();
					if(dnrs.next()){
						ds = new DatasetItem(ds_type, ds_id, dnrs.getString(1));
					}
				}else if(ds_type.contentEquals("custom")){
//					getDsName.setInt(1, ds_id);
//					dnrs = getDsName.executeQuery();
//					if(dnrs.next()){
					ds = new DatasetItem(ds_type, ds_id, null);// dnrs.getString(1));
//					}
				}
			}else{
				PreparedStatement delpstmt = con.prepareStatement("delete from ds_bookmark where id=?");
				delpstmt.setInt(1, bookmarkId);
				delpstmt.execute();
				System.out.println("deleted ds_bookmark id="+bookmarkId);
			}
			if(dnrs == null){
//				System.out.println("dnrs is null!"+ds+"|bmid:"+bookmarkId);
			}else{
				dnrs.close();
			}
//			getDsName.close();
			rs.close();
			pstmt.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ds;
	}
	/**
	 * 是否已经存在Bookmark。bookmarkName以及userName唯一确定一个bookmark.
	 * 
	 * @param bookmarkName
	 * @param userName
	 * @return
	 * @throws Throwable
	 */
	public static boolean isExist(String bookmarkName, String userName){
		int dsid = getBookmarkID(bookmarkName, userName);
		if (dsid != -1) {
			return true;
		}
		return false;
	}

	public static int getBookmarkID(String bookmarkName, String userName) {
		int dsid = -1;
//		PreparedStatement selps = con
//				.prepareStatement("SELECT id FROM custom_dataset where name = ? and user_id = ?");
		PreparedStatement selps;
		try {
			Connection con = DBConnectionFactory.getConnection();
			selps = con.prepareStatement("SELECT id FROM ds_bookmark where name = ? and user_id = ? order by id");
			int uid = UserNameIDCache.getInstance().getUserID(userName);
			selps.setString(1, bookmarkName);
			selps.setInt(2, uid);
			ResultSet selrs = selps.executeQuery();
			if (selrs.next()) {
				dsid = selrs.getInt(1);
			}
			selrs.close();
			selps.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return dsid;
	}

	/**
	 * 插入Bookmark
	 * 
	 * @param bookmarkName
	 *            新的CustomDataset名称
	 * @param userName
	 *            用户名称
	 * @param ds_type
	 *            要插入的dataset_type
	 * @param ds_id
	 *            要插入的dataset_id
	 * @param lang
	 *            当前的语言
	 * @throws Throwable
	 */
	public static void insert(String bookmarkName, String userName,
			String ds_type, int ds_id, String lang){
		try {
			Connection con = DBConnectionFactory.getConnection();
			PreparedStatement insertCustomDatasetPS = con.prepareStatement("insert into ds_bookmark (name, user_id, lang) values(?,?,?)");

			PreparedStatement insertCDDocsPS = con.prepareStatement("insert ignore into ds_bookmark_dataset (ds_bm_id, ds_id, ds_type) values (?, ?, ?)");
//			PreparedStatement getCDDocsPS = con
//					.prepareStatement("SELECT doc_id FROM custom_dataset_doc where dataset_id = ?");
			// custom dataset以及他的documents插入数据库

			boolean isNew = false;
			int new_ds_id = getBookmarkID(bookmarkName, userName);
			if (new_ds_id == -1) {
				insertCustomDatasetPS.setString(1, bookmarkName);
				insertCustomDatasetPS.setInt(2, UserNameIDCache.getInstance().getUserID(userName));
				insertCustomDatasetPS.setString(3, lang);
				insertCustomDatasetPS.executeUpdate();
				isNew = true;
				new_ds_id = getBookmarkID(bookmarkName, userName);
			}
			insertCDDocsPS.setInt(1, new_ds_id);
			insertCDDocsPS.setInt(2, ds_id);
			insertCDDocsPS.setString(3, ds_type);
			insertCDDocsPS.executeUpdate();

//			// add index for new custom dataset
//			if (isNew == true) {
//				ArrayList<DatasetItem> dss = new ArrayList<DatasetItem>();
//				dss.add(new DatasetItem("custom", new_ds_id, bookmarkName));
//				IndexManager.getIndexManager().addDatasets(dss);
//			}

			insertCustomDatasetPS.close();
			insertCDDocsPS.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Throwable e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
